package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import bean.Bean;



public class GoodsDAO extends DB {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	
	
	public int add(Bean bean)
	{
		try
		{
			conn = this.getConn();
			
			String sql = "insert into goods(name,money,num,description) " +
					"values(?,?,?,?) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, bean.getName());
			pstmt.setInt(2, bean.getMoney());
			pstmt.setInt(3, bean.getNum());
			pstmt.setString(4, bean.getDescription());

	           
			return pstmt.executeUpdate();
			
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
	}
	
	public int update(Bean bean,int id)
	{
		try
		{
			conn = this.getConn();
			
			String sql = "update goods set name=?,money=?,num=?,description=?  where id=?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, bean.getName());
			pstmt.setInt(2, bean.getMoney());
			pstmt.setInt(3, bean.getNum());
			pstmt.setString(4, bean.getDescription());
			pstmt.setInt(5, id);

	           
			return pstmt.executeUpdate();
			
		} catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
	}
	
		public int updatenumj(int id)
		{
			try
			{
				conn = this.getConn();
				
				String sql = "update goods set num = num-1  where id=?";
				
				pstmt = conn.prepareStatement(sql);
				
				pstmt.setInt(1, id);

		           
				return pstmt.executeUpdate();
				
			} catch (Exception e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
				return 0;
			}finally{
				this.closeAll(conn, pstmt, rs);
			}
		}
	
				public int updatenuma(int id)
				{
					try
					{
						conn = this.getConn();
						
						String sql = "update goods set num = num+1  where id=?";
						
						pstmt = conn.prepareStatement(sql);
						
						pstmt.setInt(1, id);

				           
						return pstmt.executeUpdate();
						
					} catch (Exception e)
					{
						// TODO Auto-generated catch block
						e.printStackTrace();
						return 0;
					}finally{
						this.closeAll(conn, pstmt, rs);
					}
				}
	public List<Bean> list()
	{
		
		List<Bean> list = new ArrayList<Bean>();	
		Bean bean = null;							
		try
		{
			conn = this.getConn();					
			String sql = "select * from goods ";
			pstmt = conn.prepareStatement(sql);		
		    rs =  pstmt.executeQuery();				
			while (rs.next())
			{
				bean= new Bean();					

				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setMoney(rs.getInt("money"));
				bean.setNum(rs.getInt("num"));
				bean.setDescription(rs.getString("description"));
				list.add(bean);
			}
			return list;		
		} catch (Exception e)
		{
			e.printStackTrace();
			return null;	
		}finally
		{
			this.closeAll(conn, pstmt, rs);	
		}
	}
	
		public List<Bean> listit0()
		{
			
			List<Bean> list = new ArrayList<Bean>();	
			Bean bean = null;							
			try
			{
				conn = this.getConn();					
				String sql = "select * from goods where num > 0";
				pstmt = conn.prepareStatement(sql);		
			    rs =  pstmt.executeQuery();				
				while (rs.next())
				{
					bean= new Bean();					

					bean.setId(rs.getInt("id"));
					bean.setName(rs.getString("name"));
					bean.setMoney(rs.getInt("money"));
					bean.setNum(rs.getInt("num"));
					bean.setDescription(rs.getString("description"));
					list.add(bean);
				}
				return list;		
			} catch (Exception e)
			{
				e.printStackTrace();
				return null;	
			}finally
			{
				this.closeAll(conn, pstmt, rs);	
			}
		}
		public Bean edit(int id)
		{
			
			Bean bean = null;							
			try
			{
				conn = this.getConn();					
				String sql = "select * from goods where id='"+ id +"' ";
				pstmt = conn.prepareStatement(sql);		
			    rs =  pstmt.executeQuery();				
				while (rs.next())
				{
					bean= new Bean();					

					bean.setId(rs.getInt("id"));
					bean.setName(rs.getString("name"));
					bean.setMoney(rs.getInt("money"));
					bean.setNum(rs.getInt("num"));
					bean.setDescription(rs.getString("description"));
				}
				return bean;		
			} catch (Exception e)
			{
				e.printStackTrace();
				return null;	
			}finally
			{
				this.closeAll(conn, pstmt, rs);	
			}
		}
		public List<Bean> search(String id)
		{
			
			List<Bean> list = new ArrayList<Bean>();	
			Bean bean = null;							
			try
			{
				conn = this.getConn();					
				String sql = "select * from client where id= '"+ id +"'";
				pstmt = conn.prepareStatement(sql);		
			    rs =  pstmt.executeQuery();				
				while (rs.next())
				{
					bean= new Bean();					

					bean.setId(rs.getInt("id"));
					bean.setName(rs.getString("name"));
					bean.setPhone(rs.getString("phone"));
					bean.setMail(rs.getString("mail"));
					bean.setScore(rs.getString("score"));
					bean.setType(rs.getString("type"));
					bean.setState(rs.getString("state"));
					bean.setDecision(rs.getString("decision"));
					list.add(bean);
				}
				return list;		
			} catch (Exception e)
			{
				e.printStackTrace();
				return null;	
			}finally
			{
				this.closeAll(conn, pstmt, rs);	
			}
		}
	public int delete(int id)
	{
		int i = 0;
		try
		{
			conn = this.getConn();
			String sql = "delete from goods where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);

			i = pstmt.executeUpdate();
			this.closeAll(conn, pstmt, rs);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return i;
	}

}
